Impressed by Disabling InnoDB Redo Logging for Creating Tables as Fast as Possible

In the last weeks / months, I have been working on understanding / improving MySQL startup with many tables.  I already wrote a post about my work (Faster MySQL Startup with Many Tables).  In that post, I link to a bug report (Bug #115988 : Too Much Disk Read on Startup, penalizing deployments with many tables).  In that bug report, I write, without much details, that the quickest way to create tables in 8.0+ is by reducing durability and disabling redo logging.  We will explore this statement in this post.  The TLDR is that I was very impressed by the speedup introduced by disabling Redo Logging, and I think there might be other situations where this is useful.

This post is written in the context of my work on MySQL at Aiven : check our Developer Center, blog or careers page to know more about us.

The usual / classic way of getting extra speed for writing data in MySQL is to set sync_binlog to 0 and innodb_flush_log_at_trx_commit to 2 (I use trx_commit for short; and yes, 0 might be even faster, but this is not often used).  This configuration is called Reduced Durability or Low Durability, I already blogged and talked about it, including the consequences of running MySQL this way.  In short, this configuration has impacts on the persistence of data, and data might be lost (not durable) after an Operating System crash.

Creating 100.000 tables with MySQL 8.0.39 on a m6id.xlarge AWS instance (4 vcpu and local SSD) with reduced durability (sync_binlog=0 and trx_commit=2) takes 5:23 (5 minutes and 23 seconds) compared to 6:25 with high durability (sync_binlog=1 and trx_commit=1).  This is an interesting speedup, but as you can guess, disabling InnoDB Redo Logging leads to much faster results.

Disabling InnoDB Redo Log is a new feature introduced in MySQL 8.0.21.  It goes one step further than reducing durability.  With reduced durability (sync_binlog=0 and trx_commit=2), a MySQL crash (as opposed to an operating system crash) does not lose data, but when disabling InnoDB Redo Logging, losing data is almost guaranteed, even with a simple MySQL crash.  So it should only be used in situations where losing data is not a problem.

Disabling Redo Logging is done by running ALTER INSTANCE DISABLE INNODB REDO_LOG.  A MySQL restart will keep Redo Logging disabled, the flip-command — ALTER INSTANCE ENABLE INNODB REDO_LOG — should be run to re-enable Redo Logging.

Creating 100.000 tables with MySQL 8.0.39 on a m6id.xlarge AWS instance with Redo Logging disabled takes 3:56 (compared to 5:23 with reduced durability and 6:25 with high durability).  I have to say that I did not expect such a speedup and was very impressed.  In the past, when loading data in MySQL, I was usually reducing durability and batching, disabling Redo Logging is now part of my list of ticks to speed things ups.

The two other situations where I think it is interesting to disable InnoDB Redo Logging is when loading a mysqldump and when catching-up on replication after restoring a backup.  Obviously, when doing this, a crash needs restarting the operation from the beginning, but such a crash is unlikely, and the extra speed is probably worth the small risk.